Version: Staging

OptOrderReplaceGateway

Records updated, or replaced into the OptionOrderReplaceGateway table are validated and then converted to SpdrParentOrder cancel/replace records and forwarded to the appropriate execution engine for futher processing.\nSee the SpiderRock Execution Engine concept guide for more details.

METADATA

Attribute	Value
Topic	5120-srse-gateway
MLink Token	SystemData
Product	SRTrade
accessType	SELECT,UPDATE,INSERT,DELETE

Table Definition

Field	Type	Key	Default Value	Comment
okey_at	enum - AssetType	PRI	'None'
okey_ts	enum - TickerSrc	PRI	'None'
okey_tk	VARCHAR(12)	PRI	''
okey_yr	SMALLINT UNSIGNED	PRI	0
okey_mn	TINYINT UNSIGNED	PRI	0
okey_dy	TINYINT UNSIGNED	PRI	0
okey_xx	DOUBLE	PRI	0
okey_cp	enum - CallPut	PRI	'Call'
accnt	VARCHAR(16)	PRI	''
spdrSource	enum - SpdrSource	PRI	'None'
groupingCode	CHAR(19)	PRI	'0000-0000-0000-0000'
clientFirm	VARCHAR(16)	PRI	''
orderNumber	BIGINT		0	SPDR order number
orderSize	INT		0	maximum fill size contracts
orderActiveSize	INT		0	total activated size total size released for execution 1 all available size
orderPrcLimit	FLOAT		0	Order price limit Valid range 0005 99990 Required if orderLimitType is Prc PrcDe PrcDeX PrcDeT PrcDeXT Ignored if orderLimitType is Market Otherwise must be set to 0
orderVolLimit	FLOAT		0	Order volatility limit Valid range 0005 9000 Required if orderLimitType is Vol VolX Ignored if orderLimitType is Market Otherwise must be set to 0
takeProbability	FLOAT		0.50	probability threshold for taking an existing public quote
makeProbability	FLOAT		0.50	probability threshold for posting in an exchange order book
strategy	VARCHAR(36)		''	Client strategy string This value will appear on the SR Trade Monitor and in execution reports
userName	VARCHAR(24)		''
orderDttm	DATETIME(6)		'2000-01-01'	Order entry time Will be set to arrival time if not supplied
positionType	enum - PositionType		'None'
ssaleFlag	enum - ShortSaleFlag		'None'	used to determine stock autohedge flags
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
okey_tk	1
okey_yr	2
okey_mn	3
okey_dy	4
okey_xx	5
okey_cp	6
okey_at	7
okey_ts	8
accnt	9
spdrSource	10
groupingCode	11
clientFirm	12

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTrade`.`MsgOptOrderReplaceGateway` (
    `okey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `okey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `okey_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `okey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `okey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `okey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `okey_xx` DOUBLE NOT NULL DEFAULT 0,
    `okey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call',
    `accnt` VARCHAR(16) NOT NULL DEFAULT '',
    `spdrSource` ENUM('None','SpdrTicket','SpdrSingle','SRSE','FIX','HedgeTool','TradeHedge','OpenHedge','AutoHedge','Orphan','RiskManager','OrderManager','ManagedOrder','RFQRespSrvr','Legger','SRSEDrop','FixDrop','TicketDrop','SysTest','RFRResponse','AllocOmni','AllocClient','CertGateway','MLegResponse','LeggerX','DropManager','AutoHedgeSrvr','AuctionStrategySrvr','AllocBlockFace','AllocBlockCust','IceChatGateway','EXS2SRC','MLinkResponse','AutoResponderVD','AutoResponderRC','AutoResponderSN','AutoResponderBX','MLink') NOT NULL DEFAULT 'None',
    `groupingCode` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '',
    `orderNumber` BIGINT NOT NULL DEFAULT 0 COMMENT 'SPDR order number',
    `orderSize` INT NOT NULL DEFAULT 0 COMMENT 'maximum fill size (contracts)',
    `orderActiveSize` INT NOT NULL DEFAULT 0 COMMENT 'total activated size (total size released for execution) (-1 = all available size)',
    `orderPrcLimit` FLOAT NOT NULL DEFAULT 0 COMMENT 'Order price limit.  Valid range: [0.005, 9999.0].  Required if `orderLimitType` is Prc, PrcDe, PrcDeX, PrcDeT, PrcDeXT.  Ignored if `orderLimitType` is Market.  Otherwise must be set to 0.',
    `orderVolLimit` FLOAT NOT NULL DEFAULT 0 COMMENT 'Order volatility limit.  Valid range: [0.005, 9.000].  Required if `orderLimitType` is Vol, VolX.  Ignored if `orderLimitType` is Market.  Otherwise must be set to 0.',
    `takeProbability` FLOAT NOT NULL DEFAULT 0.50 COMMENT 'probability threshold for taking an existing public quote',
    `makeProbability` FLOAT NOT NULL DEFAULT 0.50 COMMENT 'probability threshold for posting in an exchange order book',
    `strategy` VARCHAR(36) NOT NULL DEFAULT '' COMMENT 'Client strategy string.  This value will appear on the SR Trade Monitor and in execution reports.',
    `userName` VARCHAR(24) NOT NULL DEFAULT '',
    `orderDttm` DATETIME(6) NOT NULL DEFAULT '2000-01-01' COMMENT 'Order entry time.  Will be set to arrival time if not supplied.',
    `positionType` ENUM('None','Opening','Closing','Auto') NOT NULL DEFAULT 'None',
    `ssaleFlag` ENUM('None','Long','Short','Exempt','Auto','Open','Close','Cover','NA') NOT NULL DEFAULT 'None' COMMENT 'used to determine stock auto-hedge flags',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    CONSTRAINT nonnegative_groupingCode CHECK(ASCII(groupingCode) < 56),
    PRIMARY KEY USING HASH (`okey_tk`,`okey_yr`,`okey_mn`,`okey_dy`,`okey_xx`,`okey_cp`,`okey_at`,`okey_ts`,`accnt`,`spdrSource`,`groupingCode`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='Records updated, or replaced into the OptionOrderReplaceGateway table are validated and then converted to SpdrParentOrder cancel/replace records and forwarded to the appropriate execution engine for futher processing.\nSee the SpiderRock Execution Engine concept guide for more details.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `okey_at`,
    `okey_ts`,
    `okey_tk`,
    `okey_yr`,
    `okey_mn`,
    `okey_dy`,
    `okey_xx`,
    `okey_cp`,
    `accnt`,
    `spdrSource`,
    `groupingCode`,
    `clientFirm`,
    `orderNumber`,
    `orderSize`,
    `orderActiveSize`,
    `orderPrcLimit`,
    `orderVolLimit`,
    `takeProbability`,
    `makeProbability`,
    `strategy`,
    `userName`,
    `orderDttm`,
    `positionType`,
    `ssaleFlag`,
    `timestamp`
FROM `SRTrade`.`MsgOptOrderReplaceGateway`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `okey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `okey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `okey_tk` = 'Example_okey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `okey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `okey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `okey_cp` = 'Call'
  AND
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a ENUM('None','SpdrTicket','SpdrSingle','SRSE','FIX','HedgeTool','TradeHedge','OpenHedge','AutoHedge','Orphan','RiskManager','OrderManager','ManagedOrder','RFQRespSrvr','Legger','SRSEDrop','FixDrop','TicketDrop','SysTest','RFRResponse','AllocOmni','AllocClient','CertGateway','MLegResponse','LeggerX','DropManager','AutoHedgeSrvr','AuctionStrategySrvr','AllocBlockFace','AllocBlockCust','IceChatGateway','EXS2SRC','MLinkResponse','AutoResponderVD','AutoResponderRC','AutoResponderSN','AutoResponderBX','MLink') */ 
    `spdrSource` = 'None'
  AND
    /* Replace with a CHAR(19) */
    `groupingCode` = 'Example_groupingCode'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

UPDATE TABLE EXAMPLE QUERY

UPDATE `SRTrade`.`MsgOptOrderReplaceGateway` 
SET
    /* Replace with a BIGINT */ 
    `orderNumber` = 1234567890,
    /* Replace with a INT */ 
    `orderSize` = 5,
    /* Replace with a INT */ 
    `orderActiveSize` = 5,
    /* Replace with a FLOAT */
    `orderPrcLimit` = 1.23,
    /* Replace with a FLOAT */
    `orderVolLimit` = 1.23,
    /* Replace with a FLOAT */
    `takeProbability` = 1.23,
    /* Replace with a FLOAT */
    `makeProbability` = 1.23,
    /* Replace with a VARCHAR(36) */
    `strategy` = 'Example_strategy',
    /* Replace with a VARCHAR(24) */
    `userName` = 'Example_userName',
    /* Replace with a DATETIME(6) */
    `orderDttm` = '2022-01-01 12:34:56.000000',
    /* Replace with a ENUM('None','Opening','Closing','Auto') */ 
    `positionType` = 'None',
    /* Replace with a ENUM('None','Long','Short','Exempt','Auto','Open','Close','Cover','NA') */ 
    `ssaleFlag` = 'None',
    /* Replace with a DATETIME(6) */
    `timestamp` = '2022-01-01 12:34:56.000000'
WHERE
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `okey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `okey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `okey_tk` = 'Example_okey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `okey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `okey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `okey_cp` = 'Call'
  AND
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a ENUM('None','SpdrTicket','SpdrSingle','SRSE','FIX','HedgeTool','TradeHedge','OpenHedge','AutoHedge','Orphan','RiskManager','OrderManager','ManagedOrder','RFQRespSrvr','Legger','SRSEDrop','FixDrop','TicketDrop','SysTest','RFRResponse','AllocOmni','AllocClient','CertGateway','MLegResponse','LeggerX','DropManager','AutoHedgeSrvr','AuctionStrategySrvr','AllocBlockFace','AllocBlockCust','IceChatGateway','EXS2SRC','MLinkResponse','AutoResponderVD','AutoResponderRC','AutoResponderSN','AutoResponderBX','MLink') */ 
    `spdrSource` = 'None'
  AND
    /* Replace with a CHAR(19) */
    `groupingCode` = 'Example_groupingCode'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

INSERT TABLE EXAMPLE QUERY

INSERT INTO `SRTrade`.`MsgOptOrderReplaceGateway`(
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `okey_at`,
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `okey_ts`,
    /* Replace with a VARCHAR(12) */ 
    `okey_tk`,
    /* Replace with a SMALLINT UNSIGNED */ 
    `okey_yr`,
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_mn`,
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_dy`,
    /* Replace with a DOUBLE */ 
    `okey_xx`,
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `okey_cp`,
    /* Replace with a VARCHAR(16) */ 
    `accnt`,
    /* Replace with a ENUM('None','SpdrTicket','SpdrSingle','SRSE','FIX','HedgeTool','TradeHedge','OpenHedge','AutoHedge','Orphan','RiskManager','OrderManager','ManagedOrder','RFQRespSrvr','Legger','SRSEDrop','FixDrop','TicketDrop','SysTest','RFRResponse','AllocOmni','AllocClient','CertGateway','MLegResponse','LeggerX','DropManager','AutoHedgeSrvr','AuctionStrategySrvr','AllocBlockFace','AllocBlockCust','IceChatGateway','EXS2SRC','MLinkResponse','AutoResponderVD','AutoResponderRC','AutoResponderSN','AutoResponderBX','MLink') */ 
    `spdrSource`,
    /* Replace with a CHAR(19) */
    `groupingCode`,
    /* Replace with a VARCHAR(16) */ 
    `clientFirm`,
    /* Replace with a BIGINT */ 
    `orderNumber`,
    /* Replace with a INT */ 
    `orderSize`,
    /* Replace with a INT */ 
    `orderActiveSize`,
    /* Replace with a FLOAT */
    `orderPrcLimit`,
    /* Replace with a FLOAT */
    `orderVolLimit`,
    /* Replace with a FLOAT */
    `takeProbability`,
    /* Replace with a FLOAT */
    `makeProbability`,
    /* Replace with a VARCHAR(36) */
    `strategy`,
    /* Replace with a VARCHAR(24) */
    `userName`,
    /* Replace with a DATETIME(6) */
    `orderDttm`,
    /* Replace with a ENUM('None','Opening','Closing','Auto') */ 
    `positionType`,
    /* Replace with a ENUM('None','Long','Short','Exempt','Auto','Open','Close','Cover','NA') */ 
    `ssaleFlag`,
    /* Replace with a DATETIME(6) */
    `timestamp`
) 
VALUES(
    'None',
    'None',
    'Example_okey_tk',
    123,
    1,
    1,
    4.56,
    'Call',
    'Example_accnt',
    'None',
    'Example_groupingCode',
    'Example_clientFirm',
    1234567890,
    5,
    5,
    1.23,
    1.23,
    1.23,
    1.23,
    'Example_strategy',
    'Example_userName',
    '2022-01-01 12:34:56.000000',
    'None',
    'None',
    '2022-01-01 12:34:56.000000'
);

DELETE TABLE EXAMPLE QUERY

DELETE FROM `SRTrade`.`MsgOptOrderReplaceGateway` 
WHERE
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `okey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `okey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `okey_tk` = 'Example_okey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `okey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `okey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `okey_cp` = 'Call'
  AND
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a ENUM('None','SpdrTicket','SpdrSingle','SRSE','FIX','HedgeTool','TradeHedge','OpenHedge','AutoHedge','Orphan','RiskManager','OrderManager','ManagedOrder','RFQRespSrvr','Legger','SRSEDrop','FixDrop','TicketDrop','SysTest','RFRResponse','AllocOmni','AllocClient','CertGateway','MLegResponse','LeggerX','DropManager','AutoHedgeSrvr','AuctionStrategySrvr','AllocBlockFace','AllocBlockCust','IceChatGateway','EXS2SRC','MLinkResponse','AutoResponderVD','AutoResponderRC','AutoResponderSN','AutoResponderBX','MLink') */ 
    `spdrSource` = 'None'
  AND
    /* Replace with a CHAR(19) */
    `groupingCode` = 'Example_groupingCode'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRTrade.doccolumns WHERE TABLE_NAME='OptOrderReplaceGateway' ORDER BY ordinal_position ASC;

METADATA​

Table Definition​

PRIMARY KEY DEFINITION (Unique)​

CREATE TABLE EXAMPLE QUERY​

SELECT TABLE EXAMPLE QUERY​

UPDATE TABLE EXAMPLE QUERY​

INSERT TABLE EXAMPLE QUERY​

DELETE TABLE EXAMPLE QUERY​

Doc Columns Query​